CREATE TABLE AS

CREATE TABLE AS — Create a new table from the results of a query

Synopsis

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name

[ (column_name [, ...] ) ]

[ USING method ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

AS query

[ WITH [ NO ] DATA ]

Description

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT (though you can override the column names by giving an explicit list of new column names).

CREATE TABLE AS bears some resemblance to creating a view, but it is actually quite different: it creates a new table and evaluates the query only once to initially fill the new table. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

Parameters

GLOBAL or LOCAL

Ignored for compatibility. Use of these keywords is discouraged; see CREATE TABLE for details.

TEMPORARY or TEMP

If specified, the table is created as a temporary table. See CREATE TABLE for details.

UNLOGGED

If specified, the table is created as an unlogged table. See CREATE TABLE for details.

IF NOT EXISTS

Do not throw an error if a relation with the same name already exists. A notice is issued in this case. See CREATE TABLE for details.

table_name

The name (optionally schema-qualified) of the table to be created.

column_name

The name of a column in the new table. If not provided, column names are derived from the query's output column names.

USING method

This optional clause specifies the table access method to use for storing the contents of the new table; the method must be an access method of type TABLE. See Chapter 60 for details. If this option is not specified, the default table access method for the new table is chosen. See default_table_access_method for details.

WITH ( storage_parameter [= value] [, ... ] )

This clause specifies optional storage parameters for the new table; see Storage Parameters in CREATE TABLE for more information. For backward compatibility, the WITH clause for a table can also include OIDS=FALSE to specify that rows of the new table should not contain OIDs (object identifiers). OIDS=TRUE is no longer supported.

WITHOUT OIDS

This is backward-compatible syntax for declaring the table WITHOUT OIDS. Creating a table WITH OIDS is no longer supported.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

No special action is taken at the end of the transaction. This is the default.

DELETE ROWS

All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.

DROP

The temporary table will be dropped at the end of the current transaction block.

TABLESPACE tablespace_name

tablespace_name is the name of the tablespace in which the new table is to be created. If not specified, default_tablespace is consulted, or temp_tablespaces for temporary tables.

query

A SELECT, TABLE, or VALUES command, or an EXECUTE command that runs a prepared SELECT, TABLE, or VALUES query.

WITH [ NO ] DATA

This clause specifies whether the data produced by the query should be copied into the new table. If not, only the table structure is copied. The default is to copy the data.

Notes

This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Examples

# Create a new table films_recent consisting of only recent entries from the table films:

CREATE TABLE films_recent AS

SELECT * FROM films WHERE date_prod >= '2002-01-01';

# To fully copy a table, you can also use the short form of the TABLE command:

CREATE TABLE films2 AS

TABLE films;

# Create a new temporary table films_recent using a prepared statement, consisting of only recent entries from films. The new table will be dropped at commit time:

PREPARE recentfilms(date) AS

SELECT * FROM films WHERE date_prod > $1;

CREATE TEMP TABLE films_recent ON COMMIT DROP AS

EXECUTE recentfilms('2002-01-01');

See Also

CREATE MATERIALIZED VIEW, CREATE TABLE, EXECUTE, SELECT,SELECT INTO, VALUES